package org.compiere.process;

import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.compiere.model.MClient;
import org.compiere.model.MMailText;
import org.compiere.model.MUser;
import org.compiere.model.X_C_BPartner;
import org.compiere.model.X_R_MailText;
import org.compiere.model.X_cms_agreement_line;
import org.compiere.util.DB;
import org.compiere.util.EMail;

public class CMS_AgreementNotSigned extends SvrProcess {

	@Override
	protected void prepare() {
	}

	@Override
	protected String doIt() throws Exception {
		X_cms_agreement_line agreementLine = new X_cms_agreement_line(getCtx(), getRecord_ID(), get_TrxName());
		String returnValue = "";
		if(agreementLine.isSign_N())
		{
			String label = "Договор не одобрен";
			
			String sql = "SELECT c.DocumentNo, c.Description, l.Description FROM CMS_Agreement a " +
												"INNER JOIN CMS_Agreement_Line l ON a.CMS_Agreement_ID = l.CMS_Agreement_ID " +
												"INNER JOIN CMS_Contract c ON a.CMS_Contract_ID = c.CMS_Contract_ID " +
												"WHERE l.CMS_Agreement_Line_ID = " + getRecord_ID();
			
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			
			String message = "";
			
			String sqlUserName = "SELECT b.Name FROM CMS_Agreement_Line l " +
												"INNER JOIN AD_User u ON l.Signer::INTEGER = u.AD_User_ID " + 
												"INNER JOIN C_BPartner b ON u.C_BPartner_ID = b.C_BPartner_ID " + 
												"WHERE l.CMS_Agreement_Line_ID = " + getRecord_ID();

			String sqlSendTo = "SELECT u.AD_USER_ID FROM AD_USER u " +
													"INNER JOIN C_BPartner b ON u.C_BPartner_ID = b.C_BPartner_ID " +
													"WHERE LOWER(b.Name) LIKE '%жиенбаева%'";

			int AD_User_TO = DB.getSQLValue(get_TrxName(), sqlSendTo);
			
			if(AD_User_TO == -1) return "Отсутствуют данные для пользователя \"Юрист\"";
			
			String sqlGetZerdeAdmin = "SELECT MAX(Ad_User_ID) FROM Ad_User WHERE LOWER(Name) LIKE '%zerdea%'";
			
			int userIDFrom = DB.getSQLValue(get_TrxName(), sqlGetZerdeAdmin);

			if(userIDFrom == -1) return "Не удалось загрузить данный системы";
			
			String userName = "";
			
			try
			{
				pstmt = DB.prepareStatement(sqlUserName, get_TrxName());
				rs = pstmt.executeQuery();
				
				if(rs.next())
				{
					userName = rs.getString(1);
				}
			}
			catch(Exception e){}
			finally
			{
				rs.close(); pstmt.close();
				rs = null; pstmt = null;
			}
			
			try 
			{
				pstmt = DB.prepareStatement(sql, get_TrxName());
				rs = pstmt.executeQuery();
				
				if(rs.next())
				{
					message += "Договор № " + rs.getString(1) + "\n\r";
					message += "Предмет договора: " + rs.getString(2) + "\n\r";
					message += "Договор не одобрен пользователем - " + userName + "\n\r";
					message += "Описание проблемы: " + rs.getString(3);
				}
			}
			catch(Exception e){}
			finally
			{
				rs.close(); pstmt.close();
				rs = null; pstmt = null;
			}
			
			if(sendMail(AD_User_TO, userIDFrom, label, message))
				returnValue = "Процесс завершен успешно";		
		}

		return returnValue;
	}

	
	private boolean sendMail(int userToID, int userFromID, String label, String message) throws Exception
	{
		boolean flag = false;
		MUser userFrom = new MUser(getCtx(), userFromID, get_TrxName());
		MUser userTo = new MUser(getCtx(), userToID, get_TrxName());

		MClient client = new MClient(getCtx(), userFromID, get_TrxName());
		
		X_R_MailText rText = new X_R_MailText(getCtx(), null, get_TrxName());		
		
		StringBuffer sql = new StringBuffer();
		sql.setLength(0);
	
		sql.append("select c_bpartner_id from ad_user where ad_user_id = " + userFromID);
		int C_BPartner_ID = DB.getSQLValue(get_TrxName(), sql.toString());
		
		if(C_BPartner_ID == -1)
			throw new Exception("Ошибка получения имени отправителя");
		
		X_C_BPartner bpartner = new X_C_BPartner(getCtx(), C_BPartner_ID, get_TrxName());
		
		rText.setName(bpartner.getName());
		rText.setMailHeader(label);
		rText.setMailText(message);
		
		if(!rText.save())
			throw new Exception("Невозможно сохранить сообщение");
		
		MMailText mailText = new MMailText(getCtx(), rText.get_ID(), get_TrxName());
		
		EMail mail = client.createEMail(userFrom, userTo, mailText.getMailHeader(), mailText.getMailText());	
		
		try {
			if(EMail.SENT_OK.equals(mail.send()))
				flag = true;
		}
		catch(Exception e){}
		
		return flag;
	}
}
